
// A4_crosswalk product_id
//==============================================================================

// Description: This code crosswalks the data to product id's that were used in the
// estimation

clear
set more off

cd "D:\data_replication"

// Create company file with all aggregation levels
//==============================================================================

use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc7 = pc8plus
replace pc7 = substr(pc8plus, 1, 7) if ll == 8
sort declarant pc7
by declarant pc7: egen companies_pc7 = sum(companies_pc8plus)
by declarant pc7: keep if _n == 1
drop if ll != 8
keep declarant pc7 companies_pc7
save data\number_firms\companies_pc7.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc6 = pc8plus
replace pc6 = substr(pc8plus, 1, 6) if ll == 8
sort declarant pc6
by declarant pc6: egen companies_pc6 = sum(companies_pc8plus)
by declarant pc6: keep if _n == 1
drop if ll != 8
keep declarant pc6 companies_pc6
save data\number_firms\companies_pc6.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc5 = pc8plus
replace pc5 = substr(pc8plus, 1, 5) if ll == 8
sort declarant pc5
by declarant pc5: egen companies_pc5 = sum(companies_pc8plus)
by declarant pc5: keep if _n == 1
drop if ll != 8
keep declarant pc5 companies_pc5
save data\number_firms\companies_pc5.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc4 = pc8plus
replace pc4 = substr(pc8plus, 1, 4) if ll == 8
sort declarant pc4
by declarant pc4: egen companies_pc4 = sum(companies_pc8plus)
by declarant pc4: keep if _n == 1
drop if ll != 8
keep declarant pc4 companies_pc4
save data\number_firms\companies_pc4.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc3 = pc8plus
replace pc3 = substr(pc8plus, 1, 3) if ll == 8
sort declarant pc3
by declarant pc3: egen companies_pc3 = sum(companies_pc8plus)
by declarant pc3: keep if _n == 1
drop if ll != 8
keep declarant pc3 companies_pc3
save data\number_firms\companies_pc3.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
gen ll = length(pc8plus)
gen pc2 = pc8plus
replace pc2 = substr(pc8plus, 1, 2) if ll == 8
sort declarant pc2
by declarant pc2: egen companies_pc2 = sum(companies_pc8plus)
by declarant pc2: keep if _n == 1
drop if ll != 8
keep declarant pc2 companies_pc2
save data\number_firms\companies_pc2.dta, replace


use data\number_firms\companies_pc8plus.dta, clear
rename pc8plus pc7
rename companies_pc8plus companies_pc7
append using data\number_firms\companies_pc7.dta
rename pc7 pc6
rename companies_pc7 companies_pc6
append using data\number_firms\companies_pc6.dta
rename pc6 pc5
rename companies_pc6 companies_pc5
append using data\number_firms\companies_pc5.dta
rename pc5 pc4
rename companies_pc5 companies_pc4
append using data\number_firms\companies_pc4.dta
rename pc4 pc3
rename companies_pc4 companies_pc3
append using data\number_firms\companies_pc3.dta
rename pc3 pc2
rename companies_pc3 companies_pc2
append using data\number_firms\companies_pc2.dta
rename pc2 pc
rename companies_pc2 companies_pc

expand 2 in 1
replace pc = "292212" if _n == _N                                               // Only main product id without data -> Necessary for match
replace companies_pc = 1 if _n == _N

save data\number_firms\companies_pc.dta, replace


// Create company file for all standard product ids
//==============================================================================

forval j = 1/3817 {
use estimation\2_product_list\output\product_id_all.dta, clear
keep if product_id == `j'
merge 1:m pc using data\number_firms\companies_pc.dta 
keep if _merge == 3
sort declarant
keep product_id declarant companies_pc
rename companies_pc companies_product_id
save data\number_firms\product_files\companies_`j'.dta, replace
}


// Create company file for all composite product ids
//==============================================================================

// Get Companies main
//-------------------

set more off
forval j = 3818/4221 {
use estimation\2_product_list\output\product_id_all.dta, clear
keep if product_id == `j'
joinby pc using data\number_firms\companies_pc.dta, unmatched(none)
sort declarant pc8plus

// Get Companies sub
//------------------

rename companies_pc companies_pc_main
rename pc pc_main
rename pc8plus pc
merge m:1 pc declarant using data\number_firms\companies_pc.dta 
replace companies_pc = 1 if companies_pc == .
drop if _merge == 2

sort declarant pc
by declarant: egen companies_pc_sub = sum(companies_pc)
by declarant: keep if _n == 1

// Add up and save
//----------------

gen companies_product_id = companies_pc_main + companies_pc_sub
keep declarant product_id companies_product_id
save data\number_firms\product_files\companies_`j'.dta, replace
}


// Append all files into one and save
//==============================================================================

use data\number_firms\product_files\companies_1.dta, clear
save data\number_firms\companies_product_id.dta, replace

set more off
forval j = 2/4221 {

disp `j'
use data\number_firms\companies_product_id.dta, clear
append using data\number_firms\product_files\companies_`j'.dta
save data\number_firms\companies_product_id.dta, replace

}




